package org.jeecgframework.web.util;
import java.io.FileInputStream;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import net.sf.json.JSONArray;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jeecgframework.core.util.StringUtil;
public class ExcelImportUtil {

    /**
     * 解析excel
     * @param fileBean 参数自定义实体
     * @param page 要解析excel的页号 从0开始
     * @return
     */
	public static List<Map<String, Object>> importExcel(FileBean fileBean,int page){
		List<Map<String, Object>> dataList = new ArrayList<Map<String,Object>>();
		if(StringUtil.isNotEmpty(fileBean.getFilePath())){
			String filePathSuffix = fileBean.getFilePath().substring(fileBean.getFilePath().lastIndexOf(".") + 1);
			try {
				InputStream is = new FileInputStream(fileBean.getFilePath());
				if(filePathSuffix.equals("xls")){
					HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
					HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(page);
					for (int i = 0; i < hssfSheet.getLastRowNum() + 1; i++) {
						HSSFRow xssfRow = hssfSheet.getRow(i);
						Map<String, Object> map = new HashMap<String, Object>();
						for (int j = 0; j < xssfRow.getLastCellNum(); j++) {
							String value = getValue(xssfRow.getCell(j));
							if(StringUtil.isEmpty(value)){
								map.put(j + "", "");
								continue;
							}
							HSSFCell cell = xssfRow.getCell(j);
							if(StringUtil.isNotEmpty(cell)){
								HSSFColor color = cell.getCellStyle().getFillForegroundColorColor();
								if(StringUtil.isNotEmpty(color)){
									map.put(j+"color", color.getHexString());
								}
							}
							map.put(j + "", value);
						}
						if(map.size() > 0){
							dataList.add(map);
						}
					}
				}else if(filePathSuffix.equals("xlsx")){
					XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
					XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(page);
					for (int i = 0; i < xssfSheet.getLastRowNum() + 1; i++) {
						Map<String, Object> map = new HashMap<String, Object>();
						XSSFRow xssfRow = xssfSheet.getRow(i);
						for (int j = 0; j < xssfRow.getLastCellNum(); j++) {
							String value = getValue(xssfRow.getCell(j));
							XSSFCell cell = xssfRow.getCell(j);
							if(StringUtil.isNotEmpty(cell)){
								XSSFColor color = cell.getCellStyle().getFillForegroundColorColor();
								if(StringUtil.isNotEmpty(color)){
									map.put(j+"color", color.getARGBHex());
								}
							}
							if(StringUtil.isEmpty(value)){
								map.put(j + "", "");
								continue;
							}
							map.put(j + "", value);
						}
						if(map.size() > 0){
							dataList.add(map);
						}
					}
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return dataList;
	}
	
    @SuppressWarnings("static-access")
    private static String getValue(XSSFCell xssfRow) {
    	try {
    		if(!StringUtil.isNotEmpty(xssfRow)){
        		return "";
        	}
            if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            	return String.valueOf(xssfRow.getBooleanCellValue());
            } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
//                return String.valueOf(xssfRow.getNumericCellValue());
            	return String.valueOf(new BigDecimal(xssfRow.getNumericCellValue()));
            } else {
            	return String.valueOf(xssfRow.getStringCellValue());
            }
		} catch (Exception e) {
//			e.printStackTrace();
			return "";
		}
    }

    @SuppressWarnings("static-access")
    private static String getValue(HSSFCell hssfCell) {
    	try {
    		if(!StringUtil.isNotEmpty(hssfCell)){
        		return "";
        	}
            if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
            	return String.valueOf(hssfCell.getBooleanCellValue());
            } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
//                return String.valueOf(hssfCell.getNumericCellValue());
            	return String.valueOf(new BigDecimal(hssfCell.getNumericCellValue()));
            } else {
            	return String.valueOf(hssfCell.getStringCellValue());
            }
		} catch (Exception e) {
			return "";
		}
    }
    
    public static void main(String[] args) {
		FileBean fileBean = new FileBean();
		fileBean.setFilePath("C:\\Users\\Administrator\\Desktop\\中影广告下发平台\\newfile.xlsx");
		List<Map<String, Object>> dataList = importExcel(fileBean, 0);
		JSONArray json = JSONArray.fromObject(dataList);
		System.out.println(json.toString());
	}
}